﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace DelonixRegia.Classes.Rooms
{
    public class RoomCheckoutDBManager
    {
        private const string connectionString = "Data Source=.; Initial Catalog=DelonixRegia; Integrated Security=True;";

        public static bool Checkout(Invoice invoice)
        {
            bool result = false;
            SqlConnection conn = new SqlConnection();
            SqlCommand comm = new SqlCommand();

            try
            {
                conn.ConnectionString = connectionString;
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction();

                comm.Connection = conn;
                comm.Transaction = trans;

                comm.CommandText = "INSERT INTO Invoice(bookingID, noOfDaysStay, totalMinibarCost, totalStayCost, totalGST, netPayable) VALUES (@bookingID, @noOfDaysStay, @totalMinibarCost, @totalStayCost, @totalGST, @netPayable); SELECT SCOPE_IDENTITY();";
                comm.Parameters.AddWithValue("@bookingID", invoice.bookingID);
                comm.Parameters.AddWithValue("@noOfDaysStay", invoice.noOfDaysStay);
                comm.Parameters.AddWithValue("@totalMinibarCost", invoice.totalMinibarCost);
                comm.Parameters.AddWithValue("@totalStayCost", invoice.totalStayCost);
                comm.Parameters.AddWithValue("@totalGST", invoice.totalGST);
                comm.Parameters.AddWithValue("@netPayable", invoice.netPayable);

                object eS = comm.ExecuteScalar();
                int invoiceID = 0;
                if (eS != DBNull.Value)
                {
                    invoiceID = Convert.ToInt32(eS);
                }
                else
                {
                    comm.Transaction.Rollback();
                    conn.Close();
                    return false;
                }

                if (invoiceID != 0)
                {
                    comm.CommandText = "INSERT INTO RoomCheckout(invoiceID, dateTime, bookingID) VALUES (@invoiceID, @dateTime, @bookingID);";
                    comm.Parameters.AddWithValue("@invoiceID", invoiceID);
                    comm.Parameters.AddWithValue("@dateTime", DateTime.Now);

                    int rowsCreated = (int)comm.ExecuteNonQuery();
                    if (rowsCreated == 1)
                    {
                        result = true;
                        comm.Transaction.Commit();
                    }
                    else
                    {
                        comm.Transaction.Rollback();
                        conn.Close();
                        return false;
                    }
                }
                else
                {
                    comm.Transaction.Rollback();
                    conn.Close();
                    return false;
                }

                conn.Close();
            }
            catch (SqlException e)
            {
                comm.Transaction.Rollback();
                conn.Close();
                throw e;
            }

            return result;
        }
    }
}